package db.newversion;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Observable;

import javax.swing.JOptionPane;

import com.sun.org.apache.xpath.internal.functions.WrongNumberArgsException;

import core.Constants;
import core.Person;

public class ArchersDB extends Observable implements IDataBase < Integer, Person > {
	private static ArchersDB instance = null;
	private HashMap < Integer, Person > buffer = null;
	private HashMap < Person, Integer > buffer2 = null;
	private int latestInsertedID;

	public static ArchersDB getInstance() {
		if (instance == null) {
			instance = new ArchersDB();
		}
		return instance;
	}

	private ArchersDB() {
		try {
			this.createTable();
		} catch (SQLException e) {

		}
		try {
			this.importAllArchers();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "An error occurred when importing all players from DB", "Error", JOptionPane.ERROR_MESSAGE);
		}
		this.latestInsertedID = 0;
	}

	/**
	 * @param toInsert
	 *            the person to insert into the DB
	 * @param objects
	 *            may be null
	 */
	@Override
	public boolean insert(Person toInsert, Object... objects) throws SQLException {
		PreparedStatement psInsert = GeneralDB.getInstance().getConnection().prepareStatement("insert into " + Constants.ARCHER_TABLENAME + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		int id = getNextID();
		psInsert.setInt(1, id);
		psInsert.setString(2, toInsert.getName());
		psInsert.setString(3, toInsert.getSurName());
		psInsert.setString(4, toInsert.getNation());
		psInsert.setString(5, toInsert.getCountry());
		psInsert.setString(6, toInsert.getBowCathegory());
		psInsert.setString(7, toInsert.getAgeClass());
		psInsert.setString(8, toInsert.getClub());
		psInsert.setString(9, toInsert.getGender());
		psInsert.setBoolean(10, toInsert.isLicense());
		psInsert.setBoolean(11, toInsert.isCountryRating());
		psInsert.setString(12, toInsert.getBirthdate());
		psInsert.executeUpdate();

		GeneralDB.getInstance().getConnection().commit();

		this.buffer.put(id, toInsert);
		this.buffer2.put(toInsert, id);

		this.latestInsertedID = id;

		this.setChanged();
		this.notifyObservers(id);
		return true;
	}

	/**
	 * @param toUpdate
	 *            the Person to update in the DB
	 * @param objects
	 *            [0] the id of the person
	 */
	@Override
	public boolean update(Person toUpdate, Object... objects) throws SQLException {
		int id = Integer.parseInt(objects [0].toString());

		PreparedStatement psUpdate = GeneralDB
				.getInstance()
				.getConnection()
				.prepareStatement(
						"update " + Constants.ARCHER_TABLENAME + " set id=?, name=?, surName=?, nation=?, country=?, "
								+ "bowCathegory=?, ageClass=?, club=?, gender=?, license=?, countryRating=?, birthDate=? where id=?");

		psUpdate.setInt(1, id);
		psUpdate.setString(2, toUpdate.getName());
		psUpdate.setString(3, toUpdate.getSurName());
		psUpdate.setString(4, toUpdate.getNation());
		psUpdate.setString(5, toUpdate.getCountry());
		psUpdate.setString(6, toUpdate.getBowCathegory());
		psUpdate.setString(7, toUpdate.getAgeClass());
		psUpdate.setString(8, toUpdate.getClub());
		psUpdate.setString(9, toUpdate.getGender());
		psUpdate.setBoolean(10, toUpdate.isLicense());
		psUpdate.setBoolean(11, toUpdate.isCountryRating());
		psUpdate.setString(12, toUpdate.getBirthdate());
		psUpdate.setInt(13, id);
		psUpdate.executeUpdate();

		GeneralDB.getInstance().getConnection().commit();

		this.buffer.put(id, toUpdate);
		this.buffer2.put(toUpdate, id);

		this.setChanged();
		this.notifyObservers();

		return true;
	}

	/**
	 * @param toDelete
	 *            the person to delete from the DB
	 */
	@Override
	public boolean delete(Person toDelete, Object... objects) throws SQLException {
		int id;
		if (toDelete == null) {
			id = Integer.parseInt(objects [0].toString());
		} else {
			id = this.buffer2.get(toDelete);
		}
		PreparedStatement psDelete = GeneralDB.getInstance().getConnection().prepareStatement("delete from " + Constants.ARCHER_TABLENAME + " where id=?");

		psDelete.setInt(1, id);
		psDelete.executeUpdate();

		GeneralDB.getInstance().getConnection().commit();

		this.buffer.remove(id);
		this.buffer2.remove(toDelete);

		this.setChanged();
		this.notifyObservers();

		return true;
	}

	/**
	 * @return a HashMap of all Entries saved in the DB
	 */
	@Override
	public HashMap < Integer, Person > getAll() {
		HashMap < Integer, Person > toReturn = new HashMap < Integer, Person >();
		Iterator < Integer > iterator = this.buffer.keySet().iterator();
		while (iterator.hasNext()) {
			Integer tmp = iterator.next();
			toReturn.put(tmp, this.buffer.get(tmp));
		}
		return toReturn;
	}

	/**
	 * @param key
	 *            the id of the person
	 * @return the corresponding Person object
	 */
	@Override
	public Person getElement(Object... key) throws WrongNumberArgsException {
		if (key.length != 1) {
			throw new WrongNumberArgsException("Wrong number of arguments.");
		}
		return this.buffer.get(key [0]);
	}

	/**
	 * creates the archer table in the database of the form [id, name, surName,
	 * nation, country, bowCathegory, ageClass, club, gender, license,
	 * countryRating, birthDate]
	 */
	@Override
	public void createTable() throws SQLException {
		Statement statement = GeneralDB.getInstance().getConnection().createStatement();
		statement
				.execute("create table "
						+ Constants.ARCHER_TABLENAME
						+ "(id int, name varchar(40), surName varchar(40), nation varchar(40), country varchar(40), bowCathegory varchar(40), ageClass varchar(40), club varchar(40), gender varchar(40), license varchar(40), countryRating varchar(40), birthDate varchar(40))");
		// this.insert(Constants.DUMMY_PERSON);
	}

	/**
	 * drops the table from the database
	 */
	@Override
	public void dropTable() throws SQLException {
		Statement statement = GeneralDB.getInstance().getConnection().createStatement();
		statement.execute("drop table " + Constants.ARCHER_TABLENAME);
		instance = null;
	}

	public static int getNextID() throws SQLException {
		Statement statement = GeneralDB.getInstance().getConnection().createStatement();
		ResultSet resultSet = statement.executeQuery("SELECT id FROM " + Constants.ARCHER_TABLENAME + " ORDER BY id DESC");
		int id = 1;
		if (resultSet.next()) {
			id = resultSet.getInt(1) + 1;
		}
		return id;
	}

	/**
	 * @param entry
	 *            the entry to retrieve
	 * @return an array of Strings with unique entries
	 */
	public String [] getAll(DbEntry entry) {
		HashSet < String > entries = new HashSet < String >();
		Iterator < Person > iterator = this.buffer.values().iterator();
		while (iterator.hasNext()) {
			Person tmp = iterator.next();

			switch (entry) {
				case NATION:
					entries.add(tmp.getNation());
					break;
				case COUNTRY:
					entries.add(tmp.getCountry());
					break;
				case BOWCATHEGORY:
					entries.add(tmp.getBowCathegory());
					break;
				case AGECLASS:
					entries.add(tmp.getAgeClass());
					break;
				case CLUB:
					entries.add(tmp.getClub());
					break;
				case GENDER:
					entries.add(tmp.getGender());
					break;
			}
		}

		String [] toReturn = new String [entries.size()];
		entries.toArray(toReturn);
		return toReturn;
	}

	public int getLatestInsertedID() {
		return this.latestInsertedID;
	}

	/**
	 * @param person
	 *            the person to check
	 * @return the corresponding id
	 * @throws PersonDoesNotExistException
	 */
	private int getID(Person person) throws PersonDoesNotExistException {
		if (!this.buffer2.containsKey(person)) {
			throw new PersonDoesNotExistException();
		}
		return this.buffer2.get(person);
	}

	/**
	 * fetches all archers saved in the DB and saves them in the local buffers
	 * @throws SQLException
	 */
	private void importAllArchers() throws SQLException {
		this.buffer = new HashMap < Integer, Person >();
		this.buffer2 = new HashMap < Person, Integer >();
		ResultSet resultSet = GeneralDB.getInstance().getConnection().createStatement().executeQuery("SELECT * FROM " + Constants.ARCHER_TABLENAME + " ORDER BY id");
		while (resultSet.next()) {
			int id = resultSet.getInt(1);
			Person person = new Person(resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6), resultSet.getString(7),
					resultSet.getString(8), resultSet.getString(9), resultSet.getBoolean(10), resultSet.getBoolean(11), resultSet.getString(12));
			this.buffer.put(id, person);
			this.buffer2.put(person, id);
		}
	}
}